USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdProductDetails')
	BEGIN
		DROP  Procedure  dbo.InsUpdProductDetails
	END
GO

CREATE Procedure dbo.InsUpdProductDetails (
	@PRODUCTID				VARCHAR(10)	 = '0'
,	@PRODUCTNO				VARCHAR(50)
,	@PRODUCTDESC			VARCHAR(200)
,	@UNITPRICE				VARCHAR(20)  = '0.00'
,	@ISACTIVE				VARCHAR(10)	 = 'X'
)
AS
BEGIN

	IF ((CAST(@PRODUCTID AS INT) = 0) AND EXISTS(SELECT 1 FROM TR_PRODUCT WHERE PRODUCTNO = @PRODUCTNO))
		OR ((CAST(@PRODUCTID AS INT) > 0) AND EXISTS(SELECT 1 FROM TR_PRODUCT WHERE PRODUCTNO = @PRODUCTNO AND PRODUCTID <> @PRODUCTID))
	BEGIN
		SELECT 
			'<DATA>
				<RESPONSE>
					<RESULT>E</RESULT>
					<MESSAGE><![CDATA[Product already exists with same Product No]]></MESSAGE>
				</RESPONSE>
			</DATA>' AS Result
		RETURN
	END

	SET @ISACTIVE = CASE WHEN @ISACTIVE IN ('X', '1') THEN '1' ELSE '0' END

	BEGIN TRAN
		BEGIN TRY
			IF EXISTS(SELECT 1 FROM TR_PRODUCT WHERE PRODUCTID = @PRODUCTID) 
			BEGIN		-- UPDATE
				UPDATE TR_PRODUCT
				SET	PRODUCTNO			= @PRODUCTNO
				,	PRODUCTDESC			= @PRODUCTDESC
				,	UNITPRICE			= @UNITPRICE
				,	isActive			= @ISACTIVE
				WHERE
					ProductID			= @PRODUCTID
			END
			ELSE
			BEGIN		-- INSERT
				SELECT @PRODUCTID	= ISNULL(MAX(PRODUCTID), 0) +1 FROM TR_PRODUCT

				INSERT INTO TR_PRODUCT (ProductId, ProductNo, ProductDesc, UnitPrice, isActive)
				VALUES(
					@PRODUCTID		--	PRODUCTID
				,	@PRODUCTNO		--	PRODUCTNO
				,	@PRODUCTDESC	--	PRODUCTDESC
				,	@UNITPRICE		--	UNITPRICE
				,	@ISACTIVE		--	ISACTIVE
				)
			END

			COMMIT TRAN

			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>S</RESULT>
						<MESSAGE>Product details saved successfully</MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			SELECT 
				'<DATA>
					<RESPONSE>
						<RESULT>E</RESULT>
						<MESSAGE><![CDATA[Unable to save Product Details. '+ERROR_MESSAGE()+']]></MESSAGE>
					</RESPONSE>
				</DATA>' AS Result
		END CATCH
END
GO
